package com.icebergteam.warfacewidget.data.sql;

import java.io.File;
import java.util.ArrayList;
import java.util.Date;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import com.icebergteam.warfacewidget.AppManager;
import com.icebergteam.warfacewidget.WidgetProvider;
import com.icebergteam.warfacewidget.core.LogManager;
import com.icebergteam.warfacewidget.core.connection.NetworkManager;
import com.icebergteam.warfacewidget.core.connection.services.RSSFeedService;
import com.icebergteam.warfacewidget.core.utils.ViewUtils;

public class NewsTable extends BasicTable {
	public static String className = NewsTable.class.getCanonicalName();
	// Constants
	// Table
	public static final String TABLE_NAME	= "news";
	// Fields
	public static final String FIELD_ID				= "id ";
	public static final String FIELD_URL			= "url ";
	public static final String FIELD_HTML			= "html ";
	public static final String FIELD_TITLE = "title ";
	public static final String FIELD_DESC = "desc ";
	public static final String FIELD_PUBDATE = "pubdate ";
	public static final String FIELD_IMGSRC = "imgsrc ";
	public static final String FIELD_READ = "read ";
	public static final String FIELD_CATEGORY_NAME = "category_name ";
	public static final String FIELD_CATEGORY_DOMAIN = "category_domain ";
	public static final String FIELD_RSSDOMAIN			= "rssdomain ";
	

	public NewsTable() {
		
	}
	
	public String createTableStr() {
		LogManager.LogV(className, "createTable");
		String field_id			= FIELD_ID		+ "integer primary key autoincrement not null";
		String field_url		= FIELD_URL		+ "text";
		String field_html		= FIELD_HTML	+ "text";
		String field_title 		= FIELD_TITLE   + "text";
		String field_desc 		= FIELD_DESC	+ "text";
		String field_pubdate 	= FIELD_PUBDATE	+ "integer";
		String field_imgsrc 	= FIELD_IMGSRC	+ "text";
		String field_read		= FIELD_READ 	+ "BOOLEAN";
		String field_category_name = FIELD_CATEGORY_NAME + "text";
		String field_category_domain = FIELD_CATEGORY_DOMAIN + "text";
		String field_rssdomain = FIELD_RSSDOMAIN + "text";
	
		return AppManager.getDatabase().createTableStr(TABLE_NAME, field_id, field_url, field_rssdomain, field_html, field_title, field_desc, field_pubdate, field_category_name, field_category_domain, field_imgsrc, field_read);
	
	}
	
		
	public int getCountCurrentLocale() throws SQLException{
		try {
			return AppManager.getDatabase().getNewsTable().getCount(String.format("%s = '%s'",NewsTable.FIELD_RSSDOMAIN, NetworkManager.getUri(new RSSFeedService(), false)));
		} catch (Exception e) {
			new SQLException(e.getLocalizedMessage());
			return -1;
			
		}
		
	}
	
	public void setReadNews(String url) throws Exception{
		
						
			ContentValues dataToInsert = new ContentValues();    
			dataToInsert.put(NewsTable.FIELD_URL, url);
			dataToInsert.put(NewsTable.FIELD_READ, true);
					
			if(AppManager.getNewsSettings().isDisplayNewsFromSite()){
				Cursor cursor = AppManager.getDatabase().getNewsTable().getNewsByUrl(url);
				cursor.moveToFirst();
				if(cursor.getString(cursor.getColumnIndex(NewsTable.FIELD_HTML.trim())) != null && cursor.getInt(cursor.getColumnIndex(NewsTable.FIELD_READ.trim())) != 1){
					AppManager.getDatabase().getNewsTable().updateOrInsert( dataToInsert);
				}
				LogManager.LogVA(className, "html loaded and read", url);
			}
			else{
				AppManager.getDatabase().getNewsTable().updateOrInsert( dataToInsert);	
				LogManager.LogVA(className, "rss loaded and read", url);
			}
			
		
	}
	
	public Cursor getNews(ArrayList<String> categories) throws SQLException{
		clearHistory(AppManager.getNewsSettings().getPeriodHistory());
		
		try {
			SQLiteDatabase db = AppManager.getDatabase().getWritableDatabase();
			if(db != null && db.isOpen()){
				String mask = "";
				LogManager.LogVA(className, categories);
				if(categories != null && !categories.isEmpty()){
					mask += "(";
					for(String item: categories){
						mask += String.format("%s = '%s' OR ", NewsTable.FIELD_CATEGORY_NAME, item); 
					}
					mask = mask.substring(0, mask.lastIndexOf("OR")) + ") AND ";
				}
			
				mask+=String.format("%s = '%s'", NewsTable.FIELD_RSSDOMAIN, NetworkManager.getUri(new RSSFeedService(), false));
				LogManager.LogV(NewsTable.className, LogManager.getExecuteMethodName(), mask);
				return db.query(
					NewsTable.TABLE_NAME, 
					new String[] { NewsTable.FIELD_ID, NewsTable.FIELD_TITLE, NewsTable.FIELD_URL, NewsTable.FIELD_RSSDOMAIN , NewsTable.FIELD_PUBDATE, NewsTable.FIELD_DESC, NewsTable.FIELD_IMGSRC, NewsTable.FIELD_READ}, 
					mask,
					null,
					null, 
					null,
					String.format("%s DESC limit %s", NewsTable.FIELD_PUBDATE, AppManager.getNewsSettings().getCountViewNews())
					);
			}
		} catch (Exception e) {
			LogManager.LogEA(NewsTable.className, ViewUtils.getErrorMessage(e));
		}
		return null;
	}
	
	public ArrayList<String> getCategories() throws SQLException{
				
		try {
			SQLiteDatabase db = AppManager.getDatabase().getWritableDatabase();
			if(db != null && db.isOpen()){
						
				Cursor categoriesCursor = db.rawQuery(String.format("select %s from %s where %s = '%s' group by %s", NewsTable.FIELD_CATEGORY_NAME, NewsTable.TABLE_NAME, NewsTable.FIELD_RSSDOMAIN, NetworkManager.getUri(new RSSFeedService(), true), NewsTable.FIELD_CATEGORY_NAME),null);
				
				ArrayList<String> categoriesList = new ArrayList<String>();
				while(categoriesCursor!= null && categoriesCursor.moveToNext()){
					categoriesList.add(categoriesCursor.getString(categoriesCursor.getColumnIndex(NewsTable.FIELD_CATEGORY_NAME.trim())));
				}
				return categoriesList;
			}
		} catch (Exception e) {
			LogManager.LogE(NewsTable.className, LogManager.getExecuteMethodName(), e.getLocalizedMessage());
			e.printStackTrace();
		}
		return null;
	}
	
	public Cursor getNewsByUrl(String url) throws SQLException{
		clearHistory(AppManager.getNewsSettings().getPeriodHistory());
		try {
			SQLiteDatabase db = AppManager.getDatabase().getWritableDatabase();
			if(db != null && db.isOpen()){
				return db.query(
					NewsTable.TABLE_NAME, 
					new String[] {NewsTable.FIELD_ID, NewsTable.FIELD_TITLE, NewsTable.FIELD_URL, NewsTable.FIELD_RSSDOMAIN , NewsTable.FIELD_PUBDATE, NewsTable.FIELD_DESC, NewsTable.FIELD_IMGSRC, NewsTable.FIELD_HTML, NewsTable.FIELD_READ}, 
					String.format("%s = ?", NewsTable.FIELD_URL), 
					new String[]{url}, 
					null, 
					null,
					String.format("%s DESC limit %s", NewsTable.FIELD_PUBDATE, AppManager.getNewsSettings().getCountViewNews())
					);
			}
		} catch (Exception e) {
			LogManager.LogE(NewsTable.className, LogManager.getExecuteMethodName(), e.getLocalizedMessage());
			e.printStackTrace();
		}
		return null;
	}
	
	public Cursor getNewsByInd(int id) throws SQLException{
		clearHistory(AppManager.getNewsSettings().getPeriodHistory());
		try {
			SQLiteDatabase db = AppManager.getDatabase().getWritableDatabase();
			if(db != null && db.isOpen()){
				return db.query(
					NewsTable.TABLE_NAME, 
					new String[] {NewsTable.FIELD_ID, NewsTable.FIELD_TITLE, NewsTable.FIELD_URL, NewsTable.FIELD_RSSDOMAIN , NewsTable.FIELD_PUBDATE, NewsTable.FIELD_DESC, NewsTable.FIELD_IMGSRC, NewsTable.FIELD_HTML, NewsTable.FIELD_READ}, 
					String.format("%s = ?", NewsTable.FIELD_ID), 
					new String[]{String.format("%s", id)}, 
					null, 
					null,
					String.format("%s DESC limit %s", NewsTable.FIELD_PUBDATE, AppManager.getNewsSettings().getCountViewNews())
					);
			}
		} catch (Exception e) {
			LogManager.LogE(NewsTable.className, LogManager.getExecuteMethodName(), e.getLocalizedMessage());
			e.printStackTrace();
		}
		return null;
	}
	
	public int clearHistory( long period) throws SQLException{
		SQLiteDatabase db = AppManager.getDatabase().getWritableDatabase();
		if(db != null && db.isOpen()){
						
			Cursor cursorFiles =  db.query(
					NewsTable.TABLE_NAME, 
					new String[] { NewsTable.FIELD_URL}, 
					String.format("%s < ?", NewsTable.FIELD_PUBDATE), 
					new String[]{String.valueOf(new Date().getTime() - period)}, 
					null, 
					null,
					String.format("%s DESC limit %s", NewsTable.FIELD_PUBDATE, AppManager.getNewsSettings().getCountViewNews())
					);
			while(cursorFiles!= null && cursorFiles.moveToNext()){
				removeNewsItem(cursorFiles.getString(cursorFiles.getColumnIndex(NewsTable.FIELD_URL.trim())));
			}
			return 1;
		}
		return -1;
	}
	
	public void removeNewsItem(String url) throws SQLException{
		SQLiteDatabase db = AppManager.getDatabase().getWritableDatabase();
		if(db != null && db.isOpen()){
					
			Cursor cursor = db.rawQuery(String.format("select %s from %s where %s = '%s'", CacheTable.FIELD_FILE_URI, CacheTable.TABLE_NAME, CacheTable.FIELD_PARENT, url),null);
					
			while(cursor!= null && cursor.moveToNext()){
				String file_uri = cursor.getString(cursor.getColumnIndex(CacheTable.FIELD_FILE_URI.trim()));
				
				Cursor countFilesCursor = db.rawQuery(String.format("select count(*) from %s where %s = '%s'", CacheTable.TABLE_NAME, CacheTable.FIELD_FILE_URI, file_uri), null);
				countFilesCursor.moveToFirst();
				int countFiles = countFilesCursor.getInt(0);
				
				if(countFiles == 1 ){
					ViewUtils.deleteFile(file_uri);
				}
				
				db.delete(CacheTable.TABLE_NAME, String.format("%s = '%s' AND %s = '%s'",CacheTable.FIELD_PARENT, url, CacheTable.FIELD_FILE_URI, file_uri), null); 
			
			}
		
			db.delete(NewsTable.TABLE_NAME, String.format("%s = '%s'",NewsTable.FIELD_URL, url), null);
		}
	}
	
	public void deleteAll() throws SQLException{
		SQLiteDatabase db = AppManager.getDatabase().getWritableDatabase();
		if(db != null && db.isOpen()){
			db.delete(NewsTable.TABLE_NAME, null, null);
			db.delete(CacheTable.TABLE_NAME, String.format("%s = '%s'", CacheTable.FIELD_TYPE_PARENT, NewsTable.TABLE_NAME), null);

		}
		ViewUtils.deleteRecursive(new File(WidgetProvider.CACHE_PATH));
	}
	
	public Date getLastUpdate() throws SQLException{
		try {
			Cursor cur = AppManager.getDatabase().getWritableDatabase().rawQuery(
					String.format(
							"SELECT MAX (%s) AS \"max_date\"  " +
							"FROM %s WHERE %s = '%s'", NewsTable.FIELD_PUBDATE.trim(),  NewsTable.TABLE_NAME, NewsTable.FIELD_RSSDOMAIN, NetworkManager.getUri(new RSSFeedService(), true)),
							null);
			cur.moveToFirst();
			if(cur.getString(0) == null){
				return new Date();
			}
			return new Date(Long.parseLong(cur.getString(0)));
		} catch (Exception e) {
			LogManager.LogE(className,  LogManager.getExecuteMethodName(), e.getLocalizedMessage());
			e.printStackTrace();
		}
		return null;
	}

	@Override
	protected WhereWrapper getUpdateWhere(ContentValues cv) {
		WhereWrapper where = new WhereWrapper();
		where.setWhere( String.format("%s=?", NewsTable.FIELD_URL));
		where.addValue(cv.getAsString(NewsTable.FIELD_URL));
		
		return where;
	}

	@Override
	protected String getTableName() {
		return TABLE_NAME;
	}
}